﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ReviewCurveApp.Classes;
using System.Data;

namespace ReviewCurveApp.Contorller
{
	public class SubjectsManager
	{
        DBLoader db = new DBLoader();

        internal List<Subject> GetAllSubjects()
        {
            List<Subject> subjects = new List<Subject>();

            MyDBCommand command = new MyDBCommand();

            command.Sql = string.Format("select * from test.Subjects ");

            DataTable dt = db.Query(command);

            foreach (DataRow dr in dt.Rows)
            {
                Subject subject = new Subject();
                subject.Id = DbHelper.GetIntValueFromRow("Id", dr).Value;
                subject.Name = DbHelper.GetStringFromRow("Name", dr);
                subject.Description = DbHelper.GetStringFromRow("Description", dr);
                subject.Destination = DbHelper.GetStringFromRow("Destination", dr);
                subject.Status = (SubjectStatus)DbHelper.GetIntValueFromRow("status", dr);
                subjects.Add(subject);
            }
            return subjects;
        }

        internal bool AddSubject(Subject subject)
        {
            MyDBCommand command = new MyDBCommand();
            command.Sql = "INSERT INTO test.Subjects VALUES(NULL, @Name, @Description,@Destination,@CreatedDateTime,@status)";
            command.Parameters.Add("@Name", subject.Name);
            command.Parameters.Add("@Description", subject.Description);
            command.Parameters.Add("@Destination", subject.Destination);
            command.Parameters.Add("@CreatedDateTime", DateTime.Now);
            command.Parameters.Add("@status", subject.Status);
            return db.ExcuteQuery(command);
        }

        internal bool UpdateSubject(Subject subject)
        {
            MyDBCommand command = new MyDBCommand();
            command.Sql = "Update test.Subjects Set Name=@Name,Description=@Description,Destination=@Destination Where Id=@Id";
            command.Parameters.Add("@Name", subject.Name);
            command.Parameters.Add("@Description", subject.Description);
            command.Parameters.Add("@Destination", subject.Destination);
            command.Parameters.Add("@Id", subject.Id);
            return db.ExcuteQuery(command);
        }

        internal bool DeleteSubject1(Subject subject)
        {
            MyDBCommand command = new MyDBCommand();
            command.Sql = "delete from test.reviews Where UnitId in (select Id as unitId from test.units where subjectId=@Id)";
            command.Parameters.Add("@Id", subject.Id);
            return db.ExcuteQuery(command);

        }

        internal bool DeleteSubject(Subject delItem)
        {

            List<MyDBCommand> Commands = new List<MyDBCommand>();


            MyDBCommand command1 = new MyDBCommand();
            command1.Sql = "delete from test.reviews Where UnitId in (select Id as unitId from test.units where subjectId=@Id)";
            command1.Parameters.Add("@Id", delItem.Id);
            Commands.Add(command1);

            MyDBCommand command2 = new MyDBCommand();
            command2.Sql = "delete from test.Units Where subjectId=@Id";
            command2.Parameters.Add("@Id", delItem.Id);
            Commands.Add(command2);

            MyDBCommand command3 = new MyDBCommand();
            command3.Sql = "delete from test.subjects Where Id=@Id";
            command3.Parameters.Add("@Id", delItem.Id);
            Commands.Add(command3);

            return db.ExcuteTranscation(Commands);
        }

        internal int GetNewUnitSequence(int SubjectId)
        {
            MyDBCommand command = new MyDBCommand();

            command.Sql = string.Format("select max(sequence) as MaxId from test.units where subjectId=@subjectId ");

            command.Parameters.Add("@subjectId", SubjectId);

            DataTable dt = db.Query(command);

            if (dt.Rows.Count <= 0) return 1;

            DataRow dr = dt.Rows[0];

            int MaxId = DbHelper.GetIntValueFromRow("MaxId", dr).HasValue ? DbHelper.GetIntValueFromRow("MaxId", dr).Value : 0;

            int NewId = MaxId + 1;

            return NewId;
        }

        internal List<SubjectStatus> PickStatusGruops(List<Subject> subjects)
        {

            List<SubjectStatus> StatusGroups = new List<SubjectStatus>();

            for (int i = 0; i < subjects.Count; i++)
            {
                Subject subject = subjects[i];
                if (!StatusGroups.Contains(subject.Status)) StatusGroups.Add(subject.Status);
            }
            StatusGroups.Sort();
            return StatusGroups;
        }

        internal bool ActiveSubject(Subject subject)
        {
            List<MyDBCommand> Commands = new List<MyDBCommand>();

            MyDBCommand command1 = new MyDBCommand();
            command1.Sql = "Update test.units Set status=0 Where subjectId=@Id";
            command1.Parameters.Add("@Id", subject.Id);
            Commands.Add(command1);

            MyDBCommand command2 = new MyDBCommand();
            command2.Sql = "Update test.units Set status=1 Where subjectId=@Id and Sequence=1";
            command2.Parameters.Add("@Id", subject.Id);
            Commands.Add(command2);

            MyDBCommand command3 = new MyDBCommand();
            command3.Sql = "Update test.Subjects Set status=1 Where Id=@Id";
            command3.Parameters.Add("@Id", subject.Id);
            Commands.Add(command3);

            MyDBCommand command4 = new MyDBCommand();
            command4.Sql = "Delete from test.reviews where unitId in (select Id as unitId from test.units where subjectId=@Id)";
            command4.Parameters.Add("@Id", subject.Id);
            Commands.Add(command4);


            Unit unit = GetFirstUnit(subject);
            Task task = unit.Tasks[0];
            task.Status = TaskStatus.Started;
            MyDBCommand command5 = new MyDBCommand();
            command5.Sql = "INSERT INTO test.Reviews VALUES(NULL, @UnitId, @Remark,Null,@Sequence,NULL,@Status)";
            command5.Parameters.Add("@UnitId", task.UnitId);
            command5.Parameters.Add("@Remark", task.Description);
            //command5.Parameters.Add("@CompletedDateTime", DateTime.Today);
            command5.Parameters.Add("@Sequence", task.Sequnce);
            command5.Parameters.Add("@Status", task.Status);
            Commands.Add(command5);

            return db.ExcuteTranscation(Commands);


        }

        private Unit GetFirstUnit(Subject subject)
        {
            MyDBCommand command = new MyDBCommand();

            command.Sql = string.Format("select Id from test.units Where subjectId=@Id and Sequence=1");
            command.Parameters.Add("@Id", subject.Id);

            DataTable dt = db.Query(command);

            if (dt == null || dt.Rows.Count <= 0) return null;

            string UnitId= dt.Rows[0][0].ToString();

            UnitsManager um=new UnitsManager();

            return um.GetUnitById(UnitId);
        }

        internal bool PauseSubject(Subject subject)
        {

            List<MyDBCommand> Commands = new List<MyDBCommand>();

            MyDBCommand command1 = new MyDBCommand();
            command1.Sql = "Update test.units Set status=2 Where subjectId=@Id and status=1";
            command1.Parameters.Add("@Id", subject.Id);
            Commands.Add(command1);

            MyDBCommand command2 = new MyDBCommand();
            command2.Sql = "Update test.Subjects Set status=2 Where Id=@Id";
            command2.Parameters.Add("@Id", subject.Id);
            Commands.Add(command2);

            return db.ExcuteTranscation(Commands);

        }
    }
}
